<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class M_enrollees extends MY_Model {
	
	private $__table = 'enrollments';
	private $_salt = '';
	protected $_table = 'enrollments';
	protected $_uid = 'e_id';
	
	public function __construct()
	{
		parent::__construct();
		$this->_salt = sha1('1oyYe3u%zN9$V2fwhP5BXm8G6aQd7Lri#SCtrkj0');
	}
	
	public function get_all_enrollees($sy_from=FALSE,$sy_to=FALSE)
	{
		
		$query = 'SELECT
					sections.section_name,
					sections.section_status,
					enrollments.e_profile_id,
					enrollments.e_section_id,
					enrollments.e_enrollee_type,
					levels.level_code,
					levels.level_desc,
					enrollments.e_grade_level,
					enrollments.e_id,
					enrollments.e_sy_from,
					enrollments.e_sy_to,
					profiles.idno,
					profiles.firstname,
					profiles.lastname,
					profiles.middlename,
					FROM
					enrollments
					LEFT JOIN profiles ON enrollments.e_profile_id = profiles.profile_id
					LEFT JOIN sections ON enrollments.e_section_id = sections.id
					LEFT JOIN levels ON enrollments.e_grade_level = levels.level_id
					WHERE enrollments.e_sy_from = \''.$sy_from.'\' AND enrollments.e_sy_to = \''.$sy_to.'\'
					';
					
		$q = $this->db->query($query);
		return $q->num_rows() >= 1 ? $q->result() : FALSE;
		
		//$query = $this->db->where('e_sy_from',$sy_from)->where('e_sy_to',$sy_to)->get($this->__table);
		
		//return $query->num_rows() >= 1 ? $query->result() : FALSE;
	}
	
	
	
	/*
		query to count all enrollees for pagination
	*/	
	public function count_enrollees($sy_from=FALSE,$sy_to=FALSE)
	{
		return $this->db->where('e_sy_from',$sy_from)->where('e_sy_to',$sy_to)->count_all($this->__table);
	}
	
	/*
		query to fetch all enrollees for pagination
	*/	
	public function fetch_all_enrollees($sy_from,$sy_to,$limit, $start)
	{
        //$this->db->limit($limit, $start);
		
		$q = "SELECT
					enrollments.e_id,
					enrollments.e_created_at,
					profiles.idno,
					profiles.firstname,
					profiles.lastname,
					profiles.middlename,
					profiles.profile_id
					FROM
					enrollments
					LEFT JOIN profiles ON enrollments.e_profile_id = profiles.profile_id
					WHERE enrollments.e_sy_from = ".$sy_from." 
					AND enrollments.e_sy_to = ".$sy_to."
					AND enrollments.e_registered = 0
					LIMIT ".$start.",".$limit."
					";
		
        //$query = $this->db->where('e_sy_from',$sy_from)->where('e_sy_to',$sy_to)->get($this->__table);
		$query = $this->db->query($q);
		return $query->num_rows() >= 1 ? $query->result() : FALSE;
	}
	
	function search_enrollee($lastname, $firstname, $idno, $sy_from, $sy_to){
		$query = "SELECT
					sections.section_name,
					sections.section_status,
					enrollments.e_profile_id,
					enrollments.e_section_id,
					enrollments.e_enrollee_type,
					levels.level_code,
					levels.level_desc,
					enrollments.e_grade_level,
					enrollments.e_id,
					enrollments.e_sy_from,
					enrollments.e_sy_to,
					enrollments.e_profile_id,
					profiles.user_id,
					profiles.idno,
					profiles.firstname,
					profiles.lastname,
					profiles.middlename
				FROM
					enrollments
					LEFT JOIN profiles ON enrollments.e_profile_id = profiles.profile_id
					LEFT JOIN sections ON enrollments.e_section_id = sections.id
					LEFT JOIN levels ON enrollments.e_grade_level = levels.level_id
				WHERE 
					profiles.firstname LIKE '%$firstname%' 
						AND 
					profiles.lastname LIKE '%$lastname%' 
						AND 
					profiles.idno LIKE '%$idno%' 
						AND 
					(enrollments.e_sy_from = '$sy_from' AND enrollments.e_sy_to = '$sy_to')
						AND enrollments.e_registered = 0
					";
		$q = $this->db->query($query);
		return $q->num_rows() >= 1 ? $q->result() : FALSE;
	}
	
	function search_student($lastname, $firstname, $idno, $sy_from, $sy_to){
		$query = "SELECT
					sections.section_name,
					sections.section_status,
					enrollments.e_profile_id,
					enrollments.e_section_id,
					enrollments.e_enrollee_type,
					levels.level_code,
					levels.level_desc,
					enrollments.e_grade_level,
					enrollments.e_id,
					enrollments.e_sy_from,
					enrollments.e_sy_to,
					enrollments.e_profile_id,
					profiles.user_id,
					profiles.idno,
					profiles.firstname,
					profiles.lastname,
					profiles.middlename
				FROM
					enrollments
					LEFT JOIN profiles ON enrollments.e_profile_id = profiles.profile_id
					LEFT JOIN sections ON enrollments.e_section_id = sections.id
					LEFT JOIN levels ON enrollments.e_grade_level = levels.level_id
				WHERE 
					profiles.firstname LIKE '%$firstname%' 
						AND 
					profiles.lastname LIKE '%$lastname%' 
						AND 
					profiles.idno LIKE '%$idno%' 
						AND 
					(enrollments.e_sy_from = '$sy_from' AND enrollments.e_sy_to = '$sy_to')
						AND enrollments.e_registered = 1
					";
		$q = $this->db->query($query);
		return $q->num_rows() >= 1 ? $q->result() : FALSE;
	}
	
	public function get_enrollee_profile($id)
	{
		$sql = 'SELECT 
					   profile_id,
					   firstname as `First Name`,
					   lastname as `Last Name`,
					   middlename as `Middle Name`,
					   student_type as `Student Type`,
					   gender as `Gender`,
					   civilstatus as `Civil Status`,
					   dob as `Date of Birth`,
					   pob as `Place of Birth`,
					   age as `Age`,
					   disability as `Disability`,
					   nationality as `Nationality`,
					   religion as `Religion`,
					   contact_no as `Contact Number`,
					   email as `Email Address`,
					   present_address as `Present Address`,
					   fathername as `Father\'s Name`,
					   father_occupation as `Father\s Occupation`,
					   father_contact_no as `father\'s Contact #`,
					   mothername as `Mother\'s Name`,
					   mother_occupation as `Mother\'s Occupation`,
					   mother_contact_no as `mother\'s Contact #`,
					   parent_address as `Parent\'s Address`,
					   guardian_name as `Guardian\'s Name`,
					   relationship as `Guardian\'s Relationship`,
					   guardian_contact_no as `Guardian\'s Contac #`,
					   guardian_address as `Guardian\'s Address`,
					   last_school_name as `last School Attended`,
					   last_school_address as `Address`,
					   last_school_level as `level`,
					   last_school_year as `Year`,
					   schedule_for_testing
			FROM profiles where profile_id = ?';
		$get_profile_query = $this->db->query($sql,array($id));
		return $get_profile_query->num_rows() > 0 ? $get_profile_query->result_array() : FALSE;
	}
	
	public function get_section_by_profile($profile_id){
	  $q = "SELECT e_section_id, e_id FROM enrollments WHERE e_id = ?";
	  
		$q = $this->db->query($q, array($profile_id));
		return $q->num_rows() >= 1 ? $q->row() : FALSE;
	}
	
	function assign_student_section($enrollment_id, $section_id)
	{
		/* check if user is already assigned to previous sections*/
		$sql = "SELECT e_section_id from $this->__table WHERE e_id = ?";
		$query = $this->db->query($sql,array($enrollment_id));
		$result = ($query->num_rows() >= 1 AND intval($query->row()->e_section_id) !== 0) ? $query->row() : FALSE;
		
		
		
		if($result !== FALSE)
		{
			/* check if the section still exists before subtracting the load */
			$check_id_section_exists = $this->db->select('id')
												->where('id',$result->e_section_id)
												->get('sections');
			
			if($check_id_section_exists->num_rows() >=1)
			{
				/* if student is assigned to other sections minus the current_load of the section*/
				$update = "UPDATE sections 
						   SET current_load = (current_load -1) 
						   WHERE id = ? 
						   AND max_load != 0 
						   AND current_load != 0";
				$update_current_load = $this->db->query($update,array($result->e_section_id));
				
				if($this->db->affected_rows() >= 1)
				{
					/* add a current_load to the new designated section */
					$sql = "UPDATE sections set current_load = (current_load +1) where id = ? AND max_load != 0";
					$this->db->query($sql,array($section_id));
					
					/* update student section profile */
					$this->db->set('e_section_id',$section_id)
							 ->where('e_id', $enrollment_id)
							 ->update('enrollments');
					if($this->db->affected_rows() >= 1)
					{
						return 'SUCCESS';
					}else{
						log_message('e','unable to update student section in enrollment table');
						return FALSE;
					}
				}else{
					log_message('e','unable to update current load');
					return FALSE;
				}
			}else{
				
				return $this->__update_section($enrollment_id, $section_id);
			}
		}else{
			return $this->__update_section($enrollment_id, $section_id);
		}
	}
	
	private function __update_section($enrollment_id, $section_id)
	{
		$sql = "UPDATE sections 
				SET current_load = (current_load +1) 
				WHERE id = ? AND 
				max_load != 0";
		$this->db->query($sql,array($section_id));
		
		if($this->db->affected_rows() >= 1)
		{	
			$this->db->where('e_id', $enrollment_id)->update('enrollments', array('e_section_id' => $section_id));
			if($this->db->affected_rows() >= 1)
			{
				return 'SUCCESS';
			}else{
				log_message('e','unable to update student section in enrollment table for new student assignee');
				return FALSE;
			}		
		}else{
			log_message('e','unable to update section current load, for new student assigned to section');
			return FALSE;
		}
	}
	
	
	public function get_enrollee_profile_all($profile_id){
		$query = "SELECT 
					   profiles.profile_id,
					   enrollee_id,
					   firstname,
					   lastname,
					   middlename,
					   gender,
					   civilstatus,
					   dob,
					   pob,
					   age,
					   disability,
					   nationality,
					   religion,
					   contact_no,
					   email,
					   present_address,
					   fathername,
					   father_occupation,
					   father_contact_no,
					   mothername,
					   mother_occupation,
					   mother_contact_no,
					   parent_address,
					   guardian_name,
					   relationship,
					   guardian_contact_no,
					   guardian_address,
					   last_school_name,
					   last_school_address,
					   last_school_level,
					   last_school_year,
					   sy_from,
                       sy_to,
                       section_id,
                       other_info.level_id,
                       level_code,
                       level_desc,
                       section_name
				FROM profiles 
                LEFT JOIN (
					SELECT 
						enrollments.e_id AS enrollee_id,
						enrollments.e_section_id AS section_id, 
				        enrollments.e_sy_from AS sy_from,
				        enrollments.e_sy_to AS sy_to,
						e_profile_id AS profile_id, 
				        e_grade_level AS level_id, 
				        levels.level_code,
				        levels.level_desc,
				        sections.section_name
        			FROM enrollments 
					LEFT JOIN levels ON levels.level_id = enrollments.e_grade_level
					LEFT JOIN sections ON sections.id = enrollments.e_id
					WHERE enrollments.e_profile_id = '$profile_id'
					) AS other_info 
				ON 
					profiles.profile_id = other_info.profile_id
                WHERE profiles.profile_id = '$profile_id'";
				
		$q = $this->db->query($query);
		return $q->num_rows() >= 1 ? $q->row() : FALSE;
	}
	
	
	public function get_enrollee_fees($e_id,$sy,$type = '')
	{
		$fees = "SELECT  sef.sef_fee_name,
						sef.sef_fee_rate ,
						sef.sef_id,
						sef.sef_id as key_id
				FROM student_enrollment_fees sef
				where sef.sef_enrollment_id = ?
				AND sef.sef_schoolyear_id = ? ";
		
		if($type == 'misc'){
			$fees .= " AND sef.is_misc_fee = 1 ";
		}elseif($type == 'other'){
			$fees .= " AND sef.is_other_fee = 1 ";
		}elseif($type == 'tuit'){
			$fees .= " AND sef.is_tuition_fee = 1 ";
		}elseif($type == 'elec'){
			$fees .= " AND sef.is_elective_fee = 1 ";
		}elseif($type == 'uniform'){
			$fees .= " AND sef.is_uniform_fee = 1 ";
		}elseif($type == 'cca'){
			$fees .= " AND sef.is_cca_fee = 1 ";
		}
		
		$payments = "SELECT spr_id
					 FROM student_payment_records
   					 WHERE spr_payment_number = (SELECT max(spr_payment_number)-1 FROM student_payment_records WHERE spr_enrollment_id = ? AND spr_is_deleted = 0)
 					 AND spr_enrollment_id = ?
					 order by spr_id desc
                     limit 1
					 ";
		$query = $this->db->query($payments,array($e_id,$e_id));
		
		$spr_id = $query->num_rows() >0 ? $query->row()->spr_id : NULL; // current spr_payment_number
		
		if($spr_id == NULL)
		{
			$get_fees = $this->db->query($fees,array($e_id,$sy));
			return $get_fees->num_rows() > 0 ? $get_fees->result() : FALSE;
		}else{
			$sqlbalance = "SELECT spb_fee_name as sef_fee_name,
							   spb_remaining_balance as sef_fee_rate,
							   spb_id as sef_id,
							   spb_sef_id as key_id
						  FROM student_payment_breakdown
						  WHERE spb_spr_id = ? ";

			if($type == 'misc'){
				$sqlbalance .= " AND spb_is_misc_fee = 1 ";
			}elseif($type == 'other'){
				$sqlbalance .= " AND spb_is_other_fee = 1 ";
			}elseif($type == 'tuit'){
				$sqlbalance .= " AND spb_is_tuition_fee = 1 ";
			}elseif($type == 'elec'){
				$sqlbalance .= " AND spb_is_elective_fee = 1 ";
			}elseif($type == 'uniform'){
				$sqlbalance .= " AND spb_is_uniform_fee = 1 ";
			}elseif($type == 'cca'){
				$sqlbalance .= " AND spb_is_cca_fee = 1 ";
			}
			
				$balance = $this->db->query($sqlbalance,array($spr_id));
				if($balance->num_rows() >=1)
				{
					$balance->result();
				}else{
					$get_fees = $this->db->query($fees,array($e_id,$sy));
					return $get_fees->num_rows() > 0 ? $get_fees->result() : FALSE;
				}
			}
	}
	
	public function get_student_fees($e_id,$sy,$type = '')
	{
		$type = trim(strtolower($type));
		$fees = "SELECT  sef.sef_fee_name,
							sef.sef_fee_rate ,
							sef.sef_id
					FROM student_enrollment_fees sef
					where sef.sef_enrollment_id = ?
					AND sef.sef_schoolyear_id = ? ";
					
		if($type == 'misc'){
			$fees .= " AND sef.is_misc_fee = 1 ";
		}elseif($type == 'other'){
			$fees .= " AND sef.is_other_fee = 1 ";
		}elseif($type == 'tuit'){
			$fees .= " AND sef.is_tuition_fee = 1 ";
		//}elseif($type == 'elec'){
			//$fees .= " AND sef.is_elective_fee = 1 ";
		}elseif($type == 'cca'){
			$fees .= " AND sef.is_cca_fee = 1 ";
		}
		
		$get_fees = $this->db->query($fees,array($e_id,$sy));
		return $get_fees->num_rows() > 0 ? $get_fees->result() : FALSE;
	}
	
	public function get_available_fees($e_id)
	{
		/*$all_fee_query = $this->db->select(array('fee_rate as sef_fee_rate','fee_name as sef_fee_name'))
								  ->where('is_other_fee',1)
								  ->where('fee_stat','active')
								  ->get('fees');
		*/
		$one = 1;
		$all_fee_query = "SELECT fee_rate as sef_fee_rate, fee_name as sef_fee_name
						  FROM fees
						  WHERE is_other_fee = ?";
						  
		$all_query = $this->db->query($all_fee_query,array($one));
		
		if($all_query->num_rows() > 0)
		{
			/*
			$sel_fee_query = $this->db->select(array('sef_fee_name','sef_fee_rate'))
									  ->where('is_misc_fee',1)
									  ->where('sef_enrollment_id',$e_id)
									  ->get('student_enrollment_fees');
			*/
			$sel_fee_query = "SELECT sef_fee_rate, sef_fee_name
						  FROM student_enrollment_fees
						  WHERE is_misc_fee = ?
						  AND sef_enrollment_id = ?";
			
			$sel_query = $this->db->query($sel_fee_query,array($one,$e_id));
			
			if($sel_query->num_rows() > 0)
			{
				$all_fees = $all_query->result_array();
				$sel_fees = $sel_query->result_array();
				
				$names = array_map(function($value){
				return $value['sef_fee_name'];
				},$sel_fees);
				
				$new = array_filter($all_fees, function($value) use ($names){
				return !in_array($value['sef_fee_name'], $names);
				});
				
				return $new;
			}else{
				return FALSE;
			}
		}else{
			return FALSE;
		}
	}

	public function get_available_fees2($e_id)
	{
		/*$all_fee_query = $this->db->select(array('fee_rate as sef_fee_rate','fee_name as sef_fee_name'))
								  ->where('is_other_fee',1)
								  ->where('fee_stat','active')
								  ->get('fees');
		*/
		$one = 1;
		$all_fee_query = "SELECT fee_rate as sef_fee_rate, fee_name as sef_fee_name
						  FROM fees
						  WHERE is_other_fee = ?";
						  
		$all_query = $this->db->query($all_fee_query,array($one));
		
		if($all_query->num_rows() > 0)
		{
			/*
			$sel_fee_query = $this->db->select(array('sef_fee_name','sef_fee_rate'))
									  ->where('is_misc_fee',1)
									  ->where('sef_enrollment_id',$e_id)
									  ->get('student_enrollment_fees');
			*/
			$sel_fee_query = "SELECT sef_fee_rate, sef_fee_name
						  FROM student_enrollment_fees
						  WHERE is_other_fee = ?
						  AND sef_enrollment_id = ?";
			
			$sel_query = $this->db->query($sel_fee_query,array($one,$e_id));
			
			if($sel_query->num_rows() > 0)
			{
				$all_fees = $all_query->result_array();
				$sel_fees = $sel_query->result_array();
				
				$names = array_map(function($value){
				return $value['sef_fee_name'];
				},$sel_fees);
				
				$new = array_filter($all_fees, function($value) use ($names){
				return !in_array($value['sef_fee_name'], $names);
				});
				
				return $new;
			}else{
				return FALSE;
			}
		}else{
			return FALSE;
		}
	}

	public function get_optional()
	{
		$all_fee_query = "SELECT fee_rate as sef_fee_rate, fee_name as sef_fee_name
						  FROM fees
						  WHERE is_other_fee = ?";
						  
		$query = $this->db->query($all_fee_query,array(1));
		
		return $query->num_rows() >= 1 ? $query->result_array() : FALSE;
	}
	
	public function get_elective_fees($e_id)
	{
		$all_fee_query = "SELECT fee_rate as sef_fee_rate,fee_name as sef_fee_name
						  FROM fees
						  WHERE fee_name NOT IN (SELECT sef_fee_name FROM student_enrollment_fees
						  WHERE sef_enrollment_id = ?
						  )
						  AND is_elective_fee = 1";
						  
		$query = $this->db->query($all_fee_query,array($e_id));
		
		return $query->num_rows() >= 1 ? $query->result_array() : FALSE;
	}
	
	function assign_enrollee_to_section($e_profile_id, $section_id){
		$this->db->where('e_profile_id', $e_profile_id)->update('enrollments', array('e_section_id' => $section_id));
		return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
	}
	
	function get_enrollee_section_id($e_profile_id){
		$q = $this->db->select('e_section_id')->where('e_profile_id', $e_profile_id)->get($this->__table);
		return $q->num_rows() >= 1 ? $q->row()->e_section_id : FALSE;
	}
	
	function update_enrollee_profile_all($profile_data, $enrollment_data, $profile_id){
		$this->db->where('e_profile_id', $profile_id)->update('enrollments', $enrollment_data);
		if($this->db->affected_rows() >= 1){
			$this->db->where('profile_id', $profile_id)->update('profiles', $profile_data);
			return $this->db->affected_rows() >= 1 ? 'success' : 'profile_failed';
		}
		else
			return FALSE;
	}
	
	function update_enrollee($enrollment_data, $e_id){
		$this->db->where('e_id', $e_id)->update('enrollments', $enrollment_data);
		if($this->db->affected_rows() >= 1)
		{
			return TRUE;
		}else{
			return FALSE;
		}
	}
	
	function destroy_enrollee($enrollment_id){
		$this->db->where('e_id', $enrollment_id)->delete('enrollments');
		return $this->db->affected_rows() > 0 ? TRUE : FALSE;
	}
	
	function destroy_account($user_id, $profile_id){
		$this->db->where('profile_id', $profile_id)->delete('enrolled_subjects');
		$this->db->where('e_profile_id', $profile_id)->delete('enrollments');
		$this->db->where('id', $user_id)->delete('users');
		
		return true;
	}
	
	/*
		Adds other fees to current student account
	*/
	public function cashier_add_new_fees($data,$pb = FALSE)
	{
		if($pb !== FALSE)
		{
			$input_id = array();
			$count =0;
			foreach($data as $key => $val)
			{	unset($data['is_misc_fee']);
				unset($data['sef_created']);
				$sql = "insert into student_enrollment_fees(`sef_fee_name`,
															`sef_fee_rate`,
															`sef_enrollment_id`,
															`sef_schoolyear_id`,
															`sef_created`,
															`is_other_fee`) 
						values(?,?,?,?,NOW(),1)";
				$this->db->query($sql,$data[$count]);
				$input_id[$key]= $this->db->insert_id();
				$count++;
			}
			$counter = 0;
			foreach($pb as $key => $value)
			{
				$final_pb[$key]['spb_fee_name'] = $value['spb_fee_name'];
				$final_pb[$key]['spb_remaining_balance'] = $value['spb_remaining_balance'];
				$final_pb[$key]['spb_fee_rate'] = $value['spb_fee_rate'];
				$final_pb[$key]['spb_transacted_by'] = $value['spb_transacted_by'];
				$final_pb[$key]['spb_spr_id'] = $value['spb_spr_id'];
				$final_pb[$key]['spb_created'] = NOW;
				$final_pb[$key]['spb_stud_enrollment_id'] = $value['spb_stud_enrollment_id'];
				$final_pb[$key]['spb_is_other_fee'] = 1;
				$final_pb[$key]['spb_sef_id'] = $input_id[$counter];
				$counter++;
			}		

			$this->db->insert_batch('student_payment_breakdown',$final_pb);
			return $this->db->affected_rows() >=1 ? TRUE : FALSE;
		}else{
			$this->db->insert_batch('student_enrollment_fees',$data);
			return $this->db->affected_rows() > 0 ? TRUE : FALSE;
		}
	}
	
	/*
		Adds student electives to student current account
	*/
	public function cashier_add_new_efees($data,$pb = FALSE)
	{
		if($pb !== FALSE)
		{
			$input_id = array();
			$count =0;
			foreach($data as $key => $val)
			{	unset($data['is_elective_fee_fee']);
				unset($data['sef_created']);
				$sql = "insert into student_enrollment_fees(`sef_fee_name`,
															`sef_fee_rate`,
															`sef_enrollment_id`,
															`sef_schoolyear_id`,
															`sef_created`,
															`is_elective_fee`) 
						values(?,?,?,?,NOW(),1)";
				$this->db->query($sql,$data[$count]);
				$input_id[$key]= $this->db->insert_id();
				$count++;
			}
			$counter = 0;
			foreach($pb as $key => $value)
			{
				$final_pb[$key]['spb_fee_name'] = $value['spb_fee_name'];
				$final_pb[$key]['spb_remaining_balance'] = $value['spb_remaining_balance'];
				$final_pb[$key]['spb_fee_rate'] = $value['spb_fee_rate'];
				$final_pb[$key]['spb_transacted_by'] = $value['spb_transacted_by'];
				$final_pb[$key]['spb_spr_id'] = $value['spb_spr_id'];
				$final_pb[$key]['spb_created'] = NOW;
				$final_pb[$key]['spb_stud_enrollment_id'] = $value['spb_stud_enrollment_id'];
				$final_pb[$key]['spb_is_elective_fee'] = 1;
				$final_pb[$key]['spb_sef_id'] = $input_id[$counter];
				$counter++;
			}		

			$this->db->insert_batch('student_payment_breakdown',$final_pb);
			return $this->db->affected_rows() >=1 ? TRUE : FALSE;
		}else{
			$this->db->insert_batch('student_enrollment_fees',$data);
			return $this->db->affected_rows() > 0 ? TRUE : FALSE;
		}
	}
	
	/*
		Adds student uniform
	*/
	public function cashier_add_new_ufees($data,$pb = FALSE)
	{
		if($pb !== FALSE)
		{
			unset($data['is_uniform_fee']);
			unset($data['sef_created']);
			$sql = "insert into student_enrollment_fees(`sef_fee_name`,
														`sef_fee_rate`,
														`sef_enrollment_id`,
														`sef_schoolyear_id`,
														`sef_created`,
														`is_uniform_fee`) 
					values(?,?,?,?,NOW(),1)";
			$this->db->query($sql,$data);
			$input_id= $this->db->insert_id();


			$final_pb['spb_fee_name'] = $value['spb_fee_name'];
			$final_pb['spb_remaining_balance'] = $value['spb_remaining_balance'];
			$final_pb['spb_fee_rate'] = $value['spb_fee_rate'];
			$final_pb['spb_transacted_by'] = $value['spb_transacted_by'];
			$final_pb['spb_spr_id'] = $value['spb_spr_id'];
			$final_pb['spb_created'] = NOW;
			$final_pb['spb_stud_enrollment_id'] = $value['spb_stud_enrollment_id'];
			$final_pb['spb_is_uniform_fee'] = 1;
			$final_pb['spb_sef_id'] = $input_id;


			$this->db->insert('student_payment_breakdown',$final_pb);
			
			return $this->db->affected_rows() >=1 ? TRUE : FALSE;
		}else{
			$this->db->insert('student_enrollment_fees',$data);
			return $this->db->affected_rows() > 0 ? TRUE : FALSE;
		}
	}
	
	public function get_sum_of_fees($id,$gp,$sy)
	{
		$sql = 'SELECT sum(sef_fee_rate) as sef_fee_rate
			   FROM student_enrollment_fees
			   WHERE sef_gperiod_id = ? 
			   AND sef_schoolyear_id = ? 
			   AND sef_enrollment_id = ?';				  
		$query = $this->db->query($sql,array($gp,$sy,$id));
		if($query->num_rows() > 0)
		{
			$row = $query->row();
			return $row->sef_fee_rate;
		}else
		{
			return 'No Data';
		}
	}
	
	public function get_sum_of_paid_fees($id,$sy)
	{
	
		$sql = 'SELECT sum(spr_ammt_paid) as spr_ammt_paid
			   FROM student_payment_records
			   WHERE spr_schoolyear_id = ?
			   AND spr_enrollment_id = ?
			   AND spr_is_deleted = 0';
	
		$query = $this->db->query($sql,array($sy,$id));
		if($query->num_rows() > 0)
		{
			$row = $query->row();
			return $row->spr_ammt_paid;
		}else
		{
			return 'No Data';
		}
	}
	
	public function delete_stud_fee($sef_id,$e_id)
	{
		$query = $this->db->select('sef_fee_name,sef_fee_rate')
						  ->where('sef_enrollment_id',$e_id)
						  ->where('sef_id',$sef_id)
						  ->get('student_enrollment_fees');
		if($query->num_rows >= 1)
		{
			$result = $query->row();
			
			$this->db->where('spb_fee_name',$result->sef_fee_name)
					 ->where('spb_fee_rate',$result->sef_fee_rate)
					 ->where('spb_stud_enrollment_id',$e_id)
					 ->delete('student_payment_breakdown');
					 
			$this->db->where('sef_id',$sef_id)
					 ->where('sef_enrollment_id',$e_id)
					 ->delete('student_enrollment_fees');
					
		}else{
			$this->db->where('sef_id',$sef_id)
					 ->where('sef_enrollment_id',$e_id)
					 ->delete('student_enrollment_fees');
		}
		
		return $this->db->affected_rows() > 0 ? TRUE : FALSE;
	}
	
	public function get_enrollee_prof($e_id)
	{
		$sql= "SELECT concat(p.firstname,' ',p.lastname) as fullname,
					  e.e_student_id as idno,
					  l.level_desc,
					  r.description,
					  r.name as room_name,
					  p.profile_id,
					  e.e_id,
					  l.level_id,
					  l.grade_type,
					  s.section_name,
					  s.id as section_id,
					  u.id as u_id,
					  u.username as u_username,
					  p.email,
					  p.nationality,
					  p.gender,
					  e_enrollee_type
				FROM enrollments e
				LEFT JOIN profiles p ON p.profile_id = e.e_profile_id
				LEFT JOIN sections s ON s.id = e.e_section_id
				LEFT JOIN rooms r ON r.section_id = s.id
				LEFT JOIN levels l ON l.level_id = e.e_grade_level
				LEFT JOIN users u ON u.id = p.user_id
				WHERE e.e_id = ?";
		$query = $this->db->query($sql,array($e_id));
		return $query->num_rows() > 0 ? $query->row() : FALSE;
	}
	
	public function get_enrollee_prof_2($e_id)
	{
		$sql= "SELECT concat(p.firstname,' ',p.lastname) as fullname,
					  e.e_student_id as idno,
					  l.level_desc,
					  r.description,
					  r.name as room_name,
					  p.profile_id,
					  e.e_id,
					  l.level_id,
					  l.grade_type,
					  s.section_name,
					  s.id as section_id,
					  u.id as u_id,
					  u.username as u_username,
					  p.email,
					  p.nationality,
					  p.gender,
					  e_enrollee_type,
					  e_ay_id
				FROM enrollments e
				LEFT JOIN profiles p ON p.profile_id = e.e_profile_id
				LEFT JOIN sections s ON s.id = e.e_section_id
				LEFT JOIN rooms r ON r.section_id = s.id
				LEFT JOIN levels l ON l.level_id = e.e_grade_level
				LEFT JOIN users u ON u.id = p.user_id
				WHERE e.e_id = ?";
		$query = $this->db->query($sql,array($e_id));
		return $query->num_rows() > 0 ? $query->row() : FALSE;
	}
	
	public function check_level_fee_status($l_id)
	{
		$sql = "SELECT 	lf_fee_id,
						fee_name,
						fee_rate 
				FROM level_fees lf,fees f 
				WHERE lf.lf_fee_id = f.fee_id 
				AND lf_level_id = ?";
		$query = $this->db->query($sql,array($l_id));
		return $query->num_rows() > 0 ? $query->result() : FALSE;
	}
	
	public function get_all_scholarships()
	{
		$query = $this->db->get('student_scholarships');
		return $query->num_rows() > 0 ? $query->result() : FALSE;
	}
	
	/* Get ========================================= */
	function get($id){
		$q = $this->db->where('e_id', $id)->get('enrollments');
		return $q->num_rows() >= 1 ? $q->row() : FALSE;
	}
	
	public function get_sum_of_amount_due($id,$ay)
	{
		$sql = "SELECT sum(sef_fee_rate) as total_due 
				FROM student_enrollment_fees 
				WHERE sef_enrollment_id = ? AND sef_schoolyear_id = ?";
				
		$sql2 = "SELECT sum(sdr_amount) as total_deductions
				 FROM student_deductions_record
				 WHERE sdr_enrollment_id = ?
				 AND sdr_schoolyear_id = ? ";

		$sql3 = "SELECT sum(total_value_deducted) as total_scholarships
				 FROM student_scholarship_record
				 WHERE stud_e_id = ?
				 AND academic_year_id = ? ";

		$query = $this->db->query($sql,array($id,$ay));
		$query2 = $this->db->query($sql2,array($id,$ay));
		$query3 = $this->db->query($sql3,array($id,$ay));
		$total_amount_due =  $query->num_rows() > 0 ? $query->row()->total_due : 0;
		$total_student_deductions = $query2->num_rows() > 0 ? $query2->row()->total_deductions : 0;
		$total_student_scholarships = $query3->num_rows() > 0 ? $query3->row()->total_scholarships : 0;
		$total_deduct = $total_student_deductions + $total_student_scholarships;
		return ($total_amount_due - $total_deduct );
	}
	
	public function get_sum_of_total_payables($id,$ay)
	{
		$sql = 'SELECT sum(spm_value) as total_charge
				FROM student_payment_modes
				WHERE spm_student_id = ?
				AND spm_ay_id = ?';
		
		$total_ = $this->db->query($sql,array($id,$ay));
		if($total_->num_rows() >= 1)
		{
			return $total_->row()->total_charge;
		}else{
			return 0;
		}
	}
	
	public function by_section_id_and_gender($section_id,$gender){
	  $query = "SELECT count(e_id) as total_$gender FROM enrollments
	            LEFT JOIN profiles ON enrollments.e_profile_id = profiles.profile_id
	            WHERE enrollments.e_section_id = ?
	            AND profiles.gender = ?";
		$q = $this->db->query($query, array($section_id,$gender));
		
		if($q->num_rows() >= 1)
		{
			$var = 'total_'.$gender;
			if($q->row()->$var >= 1)
			{
				return $q->row()->$var;
			}
		}else{
			return NULL;
		}
		
	}
	
	public function by_level_id_and_section_id($level_id, $section_id){
	  $query = "SELECT count(e_id) as total_count FROM enrollments
	            WHERE enrollments.e_grade_level = ?
	            AND enrollments.e_section_id = ?";
		$q = $this->db->query($query, array($level_id, $section_id));
		return $q->num_rows() >= 1 ? $q->result() : FALSE;
	}
	
	public function get_latest_payment_record_id($e_id)
	{
		$sql2 = "SELECT max(spr_id) as current_spr_id
				 FROM student_payment_records
				 WHERE spr_enrollment_id = ?
				 AND spr_is_deleted = 0";
		$query2 = $this->db->query($sql2,array($e_id));
			
		return  $query2->row()->current_spr_id >0 ? $query2->row()->current_spr_id : FALSE;
	}
	
	public function verify_if_approved_by_registrar($id)
	{
		$sql = "SELECT registrar_approved 
				FROM enrollments
				WHERE e_id = ?
				AND e_ay_id = ?";
				
		$query = $this->db->query($sql,array($id,$this->c_sy->id));

		if($query->num_rows() >= 1)
		{
			if($query->row()->registrar_approved == 1)
			{
				return TRUE;
			}
			return FALSE;
		}else{
			return FALSE;
		}
	}

	public function check_waiver($id)
	{
		$sql = "SELECT with_weaver 
				FROM enrollments
				WHERE e_id = ?
				AND e_enrollee_type = ?
				AND e_ay_id = ?";
				
		$query = $this->db->query($sql,array($id,'new',$this->c_sy->id));

		if($query->num_rows() >= 1)
		{
			if($query->row()->with_weaver == 'yes')
			{
				return TRUE;
			}
			return FALSE;
		}else{
			return FALSE;
		}
	}
	
	public function registrar_activate_student($id)
	{
		$sql1 = 'SELECT e_id,registrar_approved FROM enrollments
				WHERE e_id = ?
				AND e_ay_id = ?';
				
		$query1 = $this->db->query($sql1,array($id,$this->c_sy->id));
		
		if($query1->num_rows() >= 1)
		{
			$result1 = $query1->row();
			if($result1->registrar_approved == 1)
			{
				return (object)array('status'=>FALSE,'message'=>'Student already on the list.');
			}else{
				$this->db->set('registrar_approved',1)->where('e_id',$result1->e_id)->update('enrollments');
				if($this->db->affected_rows() >=1)
				{
					return (object)array('status'=>TRUE,'message'=>'Student ready for payment.');
				}else{
					return (object)array('status'=>'false','message'=>'System Encountered an Error. Unable to add student to the list.');
				}
			}
		}else{
			return (object)array('status'=>'false','message'=>'System Encountered an Error. Unable to add student to the list.');
		}
	}
	
	public function registrar_create_student_login_credentials($id)
	{
		$sql = "SELECT e.e_student_id,
					   p.email,
					   concat(p.firstname,' ',p.middlename,' ',p.lastname,' ',p.name_ext) as fullname,
					   e.e_profile_id
				FROM enrollments e
				LEFT JOIN profiles p ON p.profile_id = e.e_profile_id
				WHERE e_id = ?
				AND e_ay_id = ?";
				
		$query = $this->db->query($sql,array($id,$this->c_sy->id));
	
		if($query->num_rows() >=1)
		{
			$result = $query->row();
			//generate student password
			$this->load->model('M_users');
			
			$password_generate = substr(md5(rand(100,999)),5,10);
			$data_user['username'] = $result->e_student_id;
			$data_user['hashed_password'] = $this->M_users->hash_password($password_generate);
			$data_user['userType'] = 'student';
			$data_user['userStatus'] = 'active';
			$data_user['created_at'] = NOW;

			//insert the data to user table
			$this->db->insert('users',$data_user);
			// get last inserted ID
			$student_user_id = $this->db->insert_id();
			
				//if insert was successfull
				if($this->db->affected_rows() >= 1)
				{
					//insert last inserted ID from the users table to the profiles table
					$this->db->set('user_id',$student_user_id)->where('profile_id',$result->e_profile_id)->update('profiles');
					if($this->db->affected_rows() >= 1)
					{
						$log['password'] = $password_generate;
						$log['username'] = $result->e_student_id;
						$log['fullname'] = $result->fullname;
						$log['email'] = $result->email;
						
						$log['stat'] =  TRUE;
						$log['log'] = 'Account Credentials was successfully Created';
						return (object)$log;
					}else{
						//failed in updating profile user_id
						$this->db->delete('users',$student_user_id);
						$log['stat'] =  FALSE;
						$log['log'] = 'Unable to update profile user id';
						return (object)$log;
					}
				}else{
					//insert fail return false
					$log['stat'] =  FALSE;
					$log['log'] = 'Unable to insert users to database';
					return (object)$log;
				}	
		}else{
				$log['stat'] = FALSE;
				$log['log'] = 'No data retrieved,Either Student Has not verified enrollment,or student did not finish the enrollment Process.';
				return (object)$log;
		}
	}
	
	public function check_enrollment_id($id)
	{
		$sql = 'SELECT e.e_id
				FROM enrollments e
				WHERE e.e_id = ?';
		$query = $this->db->query($sql,array($id));
		
		return $query->num_rows() >=1 ? TRUE : FALSE;
	}
	
	public function check_deleted_id($id)
	{
		$sql = 'SELECT e.e_id
				FROM enrollment_bin e
				WHERE e.e_id = ?';
		$query = $this->db->query($sql,array($id));
		
		return $query->num_rows() >=1 ? TRUE : FALSE;
	}
	
	public function check_id_number($x)
	{
		$query = $this->db->select('e_id')
						  ->where('verified',1)
						  ->where('e_archived',0)
						  ->where('e_ay_id',$this->c_sy->id)
						  ->where('e_student_id',$x)
						  ->limit(1)
						  ->get('enrollments');
	
		if($query->num_rows() >=1)
		{
			$e_id = $query->row()->e_id;
			return  $e_id !== NULL ? intval($e_id) : FALSE;
		}else{
			return FALSE;
		}
	}
	
	public function get_student_payment_mode($wazzup_id = FALSE)
	{
		if($wazzup_id == FALSE AND !is_numeric($wazzup_id))
		{
			return FALSE;
		}else{
			$sql = 'SELECT payment_mode, payment_mode_value 
					FROM enrollments
					WHERE e_id = ?
					AND e_ay_id = ?';
					
			$query = $this->db->query($sql,array($wazzup_id,$this->c_sy->id));
			
			if($query->num_rows() >= 1)
			{
				$result = $query->row();
				if($result->payment_mode == '' || $result->payment_mode == NULL || $result->payment_mode == 'none')
				{
					return FALSE;
				}else{
					return $result;
				}
			}else{
				return FALSE;
			}
		}
	}
	
	public function update_payment_plan_value($wazzup_id = FALSE, $data)
	{
		if($wazzup_id == FALSE AND !is_numeric($wazzup_id))
		{
			return FALSE;
		}else{
			$this->db->set($data)->where('e_id',$wazzup_id)->update('enrollments');
			return $this->db->affected_rows() >= 1 ? TRUE : FALSE;
		}
	}
	
	public function get_student_payment_mode_value($wazzup_id = FALSE)
	{
		if($wazzup_id == FALSE AND !is_numeric($wazzup_id))
		{
			return FALSE;
		}else{
			$sql = 'SELECT payment_mode_value 
					FROM enrollments
					WHERE e_id = ?
					AND e_ay_id = ?';
					
			$query = $this->db->query($sql,array($wazzup_id,$this->c_sy->id));
			
			if($query->num_rows() >= 1)
			{
				$result = $query->row()->payment_mode_value;
				if($result == '' OR $result == NULL OR $result == 'none')
				{
					return FALSE;
				}else{
					return $result;
				}
			}else{
				return FALSE;
			}
		}
	}
	
	public function check_student_set_fees($type)
	{
		$fees = 'SELECT sef_enrollment_id 
				FROM student_enrollment_fees
				WHERE sef_schoolyear_id = ?';
				
		if($type == 'misc'){
			$fees .= " AND sef.is_misc_fee = 1 ";
		}elseif($type == 'other'){
			$fees .= " AND sef.is_other_fee = 1 ";
		}elseif($type == 'tuit'){
			$fees .= " AND sef.is_tuition_fee = 1 ";
		}elseif($type == 'elec'){
			$fees .= " AND sef.is_elective_fee = 1 ";
		}elseif($type == 'uniform'){
			$fees .= " AND sef.is_uniform_fee = 1 ";
		}
		
		$q = $this->db->query($fees,array($this->c_sy->id));
		
		return $q->num_rows() >= 1 ? TRUE : FALSE;
	}
	
	public function get_otcp($id)
	{
		$query = $this->db->where('enrollment_id',$id)
				 ->where('schoolyear_id',$this->c_sy->id)
				 ->get('fees_over_the_counter');
	
		return $query->num_rows() >=1 ? $query->result() : FALSE;
	}
	
	public function get_af($id)
	{
		$query = $this->db->where('enrollment_id',$id)
				 ->where('schoolyear_id',$this->c_sy->id)
				 ->get('assessment_fees');
	
		return $query->num_rows() >=1 ? $query->row() : FALSE;
	}
	
	public function _insert_to_username_password_table($_user_id,$_raw_password)
	{
		$raw_password = trim($_raw_password);
		$raw_username = $_user_id;
		
		$sql = "INSERT INTO user_username_password(user_id,enc_password,created,usertype)
				VALUES (1,AES_ENCRYPT('{$raw_password}','{$this->_salt}'),now(),'student')";
	
		$this->db->query($sql);
	}
	
	public function _update_to_username_password_table($_user_id,$_raw_password, $usertype = 'student')
	{
		$raw_password = trim($_raw_password);
		$raw_username = $_user_id;
		
		$check = 'SELECT * FROM user_username_password WHERE user_id = ?';
		$run_check = $this->db->query($check,[$_user_id]);

		if($run_check->num_rows() >= 1)
		{
			$sql = "update user_username_password
					SET enc_password = AES_ENCRYPT(?,?),
					updated = ?
					WHERE user_id = ?";
		
			$this->db->query($sql,array($raw_password,$this->_salt,NOW,$_user_id));
		}else{
			$this->_insert_to_username_password_table($_user_id,$_raw_password, $usertype);
		}
	}
	
	public function get_raw_password($id)
	{
		$q = $this->db->select('AES_DECRYPT(enc_password,'.$this->_salt.') as raw_password')
				 ->where('id',$id)
				 ->where('usertype','student')
				 ->limit(1)
				 ->get('user_username_password');
	
		return $q->num_rows() >=1 ? $q->row()->raw_password : FALSE;
	}
	
	public function generate_new_student_password($id)
	{
		$new_pass_generate = substr(md5(rand(1,100).time()),4,8);
		
		
		$prof = $this->get_enrollee_prof($id);
		
		if($prof)
		{
		
			// pd($prof);
		
			if($prof->u_id == 0)
			{
				return FALSE;
			}else{
				$ci =& get_instance();
				$ci->load->model('M_users');
				$pass = $ci->M_users->hash_password($new_pass_generate);
			
				$new_cred['hashed_password'] =  $pass;
				$new_cred['updated_at'] = NOW;
				
				$this->db->set($new_cred)->where('id',$prof->u_id)->update('users');
				
				if($this->db->affected_rows() >= 1)
				{
					$this->_update_to_username_password_table($prof->u_id,$new_pass_generate);
					
					$success['fullname'] = $prof->fullname;
					$success['password'] = $new_pass_generate;
					$success['email'] = $prof->email;
					$success['username'] = $prof->u_username;
					
					return (object)$success;
				}else{
					return FALSE;
				}
			}
		}else{
			return FALSE;
		}
	
	
	}
	
	public function delete_enrollee($id,$reason = 'No Reason')
	{
		
		$q = $this->db->join('profiles p','p.profile_id=e.e_profile_id','left')
					  ->where('e_id',$id)
					  ->where('e_ay_id',$this->c_sy->id)
					  ->limit(1)
					  ->get('enrollments e');
					  
		// pd($q->result());
		/*
		if($q->num_rows() >= 1)
		{
			$prof_id = $q->row()->profile_id;
			$user_id = $q->row()->user_id;
			
			$this->db->trans_start();
			$this->db->where('e_id',$id)->limit(1)->delete('enrollments');
			
			// Do Not Delete Profiles Yet, Only User and Enrollment Entries
			//$this->db->where('profile_id',$prof_id)->limit(1)->delete('profiles');
			$this->db->where('id',$user_id)->limit(1)->delete('users');
			$this->db->trans_complete();
			
			
			return $this->db->trans_status();
			
		}else{
			return FALSE;
		}*/

		if($q->num_rows() >= 1)
		{
			$res = $q->row();
			// pd($res);
			$insert['e_id'] = $res->e_id;
			$insert['p_id'] = $res->e_profile_id;
			$insert['created'] = NOW;
			$insert['block'] = $res->block;
			$insert['student_id'] = $res->e_student_id;
			$insert['reason'] = $reason;
			$insert['deleted_by'] = $this->c_user->usertype.' - '.$this->c_user->userid;
			$insert['e_ay_id'] = $this->c_sy->id;
			$insert['e_section_id'] = $res->e_section_id;
			$insert['e_enrollee_type'] = $res->e_enrollee_type;
			$insert['e_grade_level'] = $res->e_grade_level;
			$insert['e_created_at'] = $res->e_created_at;
			$insert['e_updated_at'] = $res->e_updated_at;
			$insert['e_registered'] = $res->e_registered;
			$insert['e_archived'] = $res->e_archived;
			$insert['e_fullypaid'] = $res->e_fullypaid;
			$insert['enrollment_transaction_code'] = $res->enrollment_transaction_code;
			$insert['verified'] = $res->verified;
			$insert['mustverify'] = $res->mustverify;
			$insert['registrar_approved'] = $res->registrar_approved;
			$insert['guidance_approved'] = $res->guidance_approved;
			$insert['guidance_status'] = $res->guidance_status;
			$insert['guidance_school_records'] = $res->guidance_school_records;
			$insert['guidance_recommendation'] = $res->guidance_recommendation;
			$insert['vice_principal_interview'] = $res->vice_principal_interview;
			$insert['payment_mode'] = $res->payment_mode;
			$insert['payment_mode_value'] = $res->payment_mode_value;
			$insert['age'] = $res->age;
			$insert['schedule_for_testing'] = $res->schedule_for_testing;
			$insert['hear_from'] = $res->hear_from;
			$insert['e_p_a_id'] = $res->e_p_a_id;
			$insert['applying_level_id'] = $res->applying_level_id;
			$insert['guidance_recommend_level_id'] = $res->guidance_recommend_level_id;
			$insert['af_paid'] = $res->af_paid;
			$insert['charge_to'] = $res->charge_to;
			$insert['a_f_description'] = $res->a_f_description;
			$insert['a_f_price'] = $res->a_f_price;
			$insert['moved_to'] = $res->moved_to;
			$insert['with_weaver'] = $res->with_weaver;
			$insert['id_generated'] = $res->id_generated;
			
			$this->db->trans_start();
			
			//before deleting check the section and remove the load from section
			if($res->e_section_id > 0)
			{
				$sql = "UPDATE sections set current_load = (current_load -1) WHERE id = ? AND max_load != 0";
				$this->db->query($sql,array($res->e_section_id));
			}

			//move to enrollment bin
			$this->db->insert('enrollment_bin',$insert);
			//delete from enrollment table
			$this->db->where('e_id',$id)->limit(1)->delete('enrollments');
			
			// Do Not Delete Profiles Yet, Only User and Enrollment Entries
			//$this->db->where('profile_id',$prof_id)->limit(1)->delete('profiles');
			//delete user (login credentials)
			$this->db->where('id',$res->user_id)->limit(1)->delete('users');
			$this->db->trans_complete();

			return $this->db->trans_status();
		}else{
			return FALSE;
		}
	}
	
	public function get_sum_of_student_electives($e_id)
	{
		$q = $this->db->select('sum(sef_fee_rate) as total_electives',FALSE)
				 ->where('sef_enrollment_id',$e_id)
				 ->where('sef_schoolyear_id',$this->c_sy->id)
				 ->where('is_other_fee',1)
				 ->get('student_enrollment_fees');
		return $q->num_rows() >= 1 ? $q->row()->total_electives : 0;	 
	}

	public function get_sum_of_student_miscelaneous($e_id)
	{
		$q = $this->db->select('sum(sef_fee_rate) as total_misc',FALSE)
				 ->where('sef_enrollment_id',$e_id)
				 ->where('sef_schoolyear_id',$this->c_sy->id)
				 ->where('is_misc_fee',1)
				 ->get('student_enrollment_fees');
		return $q->num_rows() >= 1 ? $q->row()->total_misc : 0;	 
	}

	public function check_p_a_id($data)
	{
		$q = $this->db->select('e_id')
				 					->where('e_p_a_id ',$data);
		return $q->num_rows() >= 1 ? TRUE:FALSE;	 
	}

	public function check_needed_files($e_id)
	{
		$sql= "SELECT enrollment_file_checklist.*,
									profiles.nationality
					 FROM enrollments
					 LEFT JOIN enrollment_file_checklist ON enrollment_file_checklist.e_id = enrollments.e_id
					 LEFT JOIN profiles ON profiles.profile_id = enrollments.e_profile_id
					 WHERE enrollments.e_id = ?";
		$query = $this->db->query($sql,array($e_id));
		return $query->num_rows() > 0 ? $query->row() : FALSE;
	}
	
	/**
	 * Get Student Having Payment total of greater than 5000
	 *@return array of enrollment_ids
	 */
	public function get_paidstudent_inarray()
	{
		$e_ids = "SELECT 
						 sum(spr_ammt_paid) as total,
						 spr_enrollment_id,
						 spm_value
				  FROM student_payment_records
				  LEFT JOIN student_payment_modes ON spm_student_id = spr_enrollment_id
				  WHERE month_name = 'January Registration'
				  OR month_name = 'employee'
				  AND spr_schoolyear_id = ?
				  group by spr_enrollment_id
				  HAVING total >= 5000";
		$q = $this->db->query($e_ids, array($this->c_sy->id));

		if($q->num_rows()<=0){
			return false;
		}
		$ids = array();
		foreach ($q->result() as $key => $value) {
			$ids[]	= $value->spr_enrollment_id;	
		}

		return $ids;
	}
	
	/**
	 * Get All enrollment students for dropdown
	 */
	public function get_student_for_dd()
	{
		$sql = "
					SELECT 
						concat_ws('',p.lastname,' , ',p.firstname,' ',p.middlename) as fullname,
						e.e_student_id as idno,
						e.e_id,
						l.level_desc as level_code,
						p.nationality,
						bs.block_name,
						sec.section_name
					FROM enrollments e
					LEFT JOIN profiles p ON e.e_profile_id = p.profile_id
					LEFT JOIN levels l ON e.e_grade_level = l.level_id
					LEFT JOIN block_section bs ON e.block = bs.id
					LEFT JOIN sections sec ON e.e_section_id = sec.id
					WHERE e.e_ay_id = ?
					AND e.guidance_approved = ?
					AND e.e_registered = ?
					ORDER BY fullname;
		";
		
		$rs = $this->query($sql, array($this->c_sy->id,1,1));

		$sly[''] = "Select Student";

		if($rs){
			foreach ($rs as $key => $s) {
				$sly[$s->fullname] = $s->fullname;
			}
		}
		
		return $sly;
	}
}
?>
